package homework.jdbc.dao;

import homework.jdbc.domain.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.PriorityQueue;

/**
 * Created by Administrator on 2017/9/13.
 */
public class UserDao {
    /**
     * 查询
     */
    public List<User> findAll(){
          //获得数据库连接
        Connection conn = JDBCUtils.getConnection();
        //创建一个集合，用于将对象添加进行
        List<User> list = new ArrayList<>();
        ResultSet result = null;
        Statement statement =null;
        //以下执行
        try{
             statement = conn.createStatement();
            String sql = "select * from user";
             result = statement.executeQuery(sql);
            while (result.next()){
                User user = new User();
                user.setId(result.getInt(1));
                user.setUsername(result.getString(2));
                user.setPassword(result.getString(3));
                user.setEmail(result.getString(4));
                user.setPhone(result.getString(5));

                list.add(user);
            }
            return list;

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            //关闭资源
            if(result != null){
                try{
                    result.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if (statement != null){
                try{
                    statement.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(conn != null){
                JDBCUtils.close();
            }
        }
        return null;
    }
    public boolean insert(User user){
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into user(id,username,password,email,phone) values(?,?,?,?,?)";
        PreparedStatement ps = null;
        try{
           ps = conn.prepareStatement(sql);
           ps.setInt(1,user.getId());
           ps.setString(2,user.getUsername());
           ps.setString(3,user.getPassword());
           ps.setString(4,user.getEmail());
           ps.setString(5,user.getPhone());
           int num = ps.executeUpdate(sql);

           if(num ==1){
               return true;
           }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
           if(ps != null){
     try{
         ps.close();
     }catch (SQLException e){
         e.printStackTrace();
     }
           }

            //关闭资源
        }
        return  false;
    }
    public boolean update(User user){
        Connection conn = JDBCUtils.getConnection();
        String sql = "update user set username = ?, password = ?, email = ?, phone = ? where id = ?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getEmail());
            ps.setString(4, user.getPhone());
            ps.setInt(5, user.getId());

            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }
    public boolean delete(int id){
        Connection conn = JDBCUtils.getConnection();
        String sql = "delete from user where id = ?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);

            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }
    public User findById(int id){
        //获得数据库连接
        Connection conn = JDBCUtils.getConnection();
        //
        User user = new User();

        ResultSet result = null;
        //Statement statement =null;
        //使用预编译的PreparedStatement
        PreparedStatement ps =null;
        //以下执行
        try{
            //编写sql语句？ 是占位符
            String sql = "select * from user where id = ?";
            //SQL语句交给ps进行处理
            ps = conn.prepareStatement(sql);
            //设置sql语句的参数
            ps.setInt(1,id);
            //执行查询
            result = ps.executeQuery();
            //处理结果
            while (result.next()){
                user.setId(result.getInt(1));
                user.setUsername(result.getString(2));
                user.setPassword(result.getString(3));
                user.setEmail(result.getString(4));
                user.setPhone(result.getString(5));
                return user;
            }


        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            //关闭资源
            if(result != null){
                try{
                    result.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if (ps != null){
                try{
                    ps.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(conn != null){
                JDBCUtils.close();
            }
        }

        return null;
    }


}
